package com.codez.gznx.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;

import lombok.extern.slf4j.Slf4j;


/**
 *  excel上传 --> 实体类  < 实体类中字段需要使用@ExcelField注解和excel表格头对应 >
 * @author  zhengdechao
 * 
 */

@Service
@Slf4j
public class ExcelUtils {
	
//	@Autowired
//	public DictionaryService dictService;
	
	private static final String ROW_COMBINE = "ROW_COMBINE";	

	private static final String ROW_COLUMN_COMBINE = "ROW_COLUMN_COMBINE";	

	private static final String COLUMN_COMBINE = "COLUMN_COMBINE";	

	private static final String NOT_COMBINE = "NOT_COMBINE";	
	
	/** 默认标题所在行索引,便于找到相应的数据行索引,注意标题是跨行的时候,记得是最后一行的索引 , 0表示第一行是标题并且不跨行 */
	private static final Integer DEFAULT_TITLE_LAST_ROW_INDEX = 0 ;
	/** 默认的SHEET索引 */
	private static final Integer DEFAULT_SHEET_INDEX = 0 ;
	

	public List<?> toBeanList(InputStream inputStream , Class<? extends ValidableEntity> clazz) throws Exception {
		return toBeanList(WorkbookFactory.create(inputStream), clazz);
	}
	public List<?> toBeanList(InputStream inputStream , Class<? extends ValidableEntity> clazz, Integer titleRowIndex) throws Exception {
		return toBeanList(WorkbookFactory.create(inputStream), clazz , titleRowIndex == null ? DEFAULT_TITLE_LAST_ROW_INDEX : titleRowIndex);
	}
	
	/**
	 * --不指定标题行的情况下,使用默认定义的第一行为标题
	 * @param workbook
	 * @param clazz
	 * @return
	 * @throws Exception
	 */
	public List<?> toBeanList(Workbook workbook , Class<? extends ValidableEntity> clazz) throws Exception {
		return toBeanList(workbook, clazz , DEFAULT_TITLE_LAST_ROW_INDEX);
	}
	
	public List<?> toBeanList(Workbook workbook , Class<? extends ValidableEntity> clazz , Integer titleRowIndex) throws Exception {
		
		List<ValidableEntity> list = new LinkedList<ValidableEntity>();
		ExcelClass excelClass = null;
		boolean autoLookupTitleRowIndex = true; // 自动标识列,不指定标题列的时候需要自动获取
		if(titleRowIndex == null) {
			titleRowIndex = DEFAULT_TITLE_LAST_ROW_INDEX;
		}
		int sheetIndex = DEFAULT_SHEET_INDEX;

		/*获取到标题行索引和sheet索引*/
		if(clazz.isAnnotationPresent(ExcelClass.class)) {
			excelClass = clazz.getAnnotation(ExcelClass.class);
		}
		if(excelClass != null && excelClass.titleRowIndex() != -1){
			autoLookupTitleRowIndex = false; // 指定了数据行,不在进行自动获取
			titleRowIndex = clazz.getAnnotation(ExcelClass.class).titleRowIndex();
		}
		if(excelClass != null && excelClass.sheetIndex() != -1){
			titleRowIndex = clazz.getAnnotation(ExcelClass.class).sheetIndex();
		}
		
		Sheet sheet = workbook.getSheetAt(sheetIndex);
		// 当不存在标题的时候,在进行单独处理 titleRowIndex==-1,然后数据行直接从0开始
		Row row = sheet.getRow(titleRowIndex);
		if(autoLookupTitleRowIndex) {
			// 自动获取列,当前太复杂的表,不通过数据监测来自动获取感觉难度有点大
			// 自动获取规则,在不存在交叉跨行列,重复跨行列的时候获取当前最大跨行值,即可
			// 获取后需要跳转到最后一行标题,因为很多情况下都是上面是大标题,下面是小标题
			int maxRowspan = 0;
			for (Cell titleCell : row) {
				int currentRowspan = getCurrRowspan(sheet , titleCell);
//				log.debug("当前"+titleCell.getStringCellValue()+" 跨度为"+currentRowspan);
				if(maxRowspan < currentRowspan)
					maxRowspan = currentRowspan;
			}
			log.debug("当前行索引 " + titleRowIndex + ", 最大跨度为" + maxRowspan);
			if(maxRowspan > 0) {
				//存在跨度 , 跳转到最后一列,默认是采用最后一列为标题列,如果不是,这里也可以判断没得列合并的一行为标题列,暂时不考虑
				return toBeanList(workbook, clazz, titleRowIndex + maxRowspan); 
			}
		}
		
		int total = sheet.getLastRowNum();
		if (total - titleRowIndex < 1) {
			log.info("excel表格没有可用数据");
			return list;
		}
			
		Field[] fields = clazz.getDeclaredFields();
		Map<String, Field> fieldsMap = new LinkedHashMap<String, Field>();
//		Map<String, String> dictcodeMap = new LinkedHashMap<String, String>();
		List<Field> specificCellValueFields = new LinkedList<Field>();
		for (Field field : fields) {
			field.setAccessible(true);
			ExcelField excelField = field.getAnnotation(ExcelField.class);
			if(excelField != null){
				fieldsMap.put(excelField.value(), field);
//				if(!Utils.isEmpty(excelField.dict()))
//					dictcodeMap.put(excelField.value(), excelField.dict());
				if(excelField.rowIndex() != -1 && excelField.colIndex() != -1){
					specificCellValueFields.add(field);
				}
			}
		}
		
		if(ObjectUtils.isEmpty(fieldsMap)){
			throw new IllegalArgumentException( clazz + " -> 实体类没有任何ExcelField注解 ");
		}

		// 解析数据行
		for (int i = titleRowIndex + 1; i <= total; i++) {
			row = sheet.getRow(i);
			ValidableEntity entity = clazz.newInstance();
			if(row == null) continue;//空行
			for (Cell cell : row) {
				
				String title = getCellValue(sheet , titleRowIndex , cell.getColumnIndex());
				Field field = fieldsMap.get(title);
				try {
					//换一种思路 获取到field
					if(field == null){
						// 说明没有找到 , 可能是采用AZ索引  
						// 直接和标题无关 ,, 使用列索引  A B C D
						field = fieldsMap.get(AzUtil.getExcelStr(cell.getColumnIndex()));
					}
//					if(field == null){
//						// 暂时不使用列明+AZ模式,毕竟暂时没得这样的场景
//						field = fieldsMap.get(getTitle(temp.getColumnIndex()) + getAZByIndex(temp.getColumnIndex()));
//					}

					if (field != null) {
						String value = getCellValue(sheet , cell);
						if(!Utils.isEmpty(value)){
							// 字典转换成对应的值
//							dictcode = dictcodeMap.get(getTitle(temp.getColumnIndex()));
//							if(!Utils.isEmpty(dictcode)){
//								value = getDictvalue(value , dictcode);
//							}
							field.set(entity, getTypeValue(value, field.getType(), title));
						}
					}
				} catch (ValidateEntityException e) {
					entity.addError(field.getName() , MessageFormat.format(e.getMessage(), title));
				} catch (IndexOutOfBoundsException e) {
					e.printStackTrace();
					log.error("现在新方法应该不会出现了....可能是后面的空白单元格读取到信息导致索引超index...");
				} 
			}
			
			//出现在这里的,,逻辑应该就不会出现在row-cell中
			if(specificCellValueFields.size() > 0){
				for (Field specificCellValueField : specificCellValueFields) {
					try {
						String value = getCellValue(sheet,
								specificCellValueField.getAnnotation(ExcelField.class).rowIndex(),
								specificCellValueField.getAnnotation(ExcelField.class).colIndex());
						
//						specificCellValueField.set(entity, getTypeValue(value, specificCellValueField.getType()
//								,specificCellValueField.getName())); 
						specificCellValueField.set(entity, getTypeValue(value, specificCellValueField.getType()
								,specificCellValueField.getAnnotation(ExcelField.class).value()));
						
					} catch (ValidateEntityException e) {
						entity.addError(specificCellValueField.getName() , 
								MessageFormat.format(e.getMessage(), specificCellValueField.getAnnotation(ExcelField.class).value()));
					}
				}
			}
			//设置行号
			if(entity instanceof ExcelHelper) {
				((ExcelHelper)entity).setRowNum(i);
			}
			list.add(entity);
		}
		return list;
		
	}
	
//	String getDictvalue(String value , String dictcode){
//		if(!Utils.isEmpty(dictcode)){
//			String object = dictService.getDictvalue(dictcode.toUpperCase(), value);
//			if(object == null)
//				throw new ValidateEntityException("{%s_不存在的类型}");
//			return object.toString();
//		}
//		return value;
//	}
	
	private Object getTypeValue(String cellValue, Class<?> type , String fieldName) throws ValidateEntityException {
		try {
			if (String.class == type)
	        {
	            return String.valueOf(cellValue);
	        }
	        else if ((Integer.TYPE == type) || (Integer.class == type))
	        {
	            return Integer.parseInt(cellValue);
	        }
	        else if ((Long.TYPE == type) || (Long.class == type))
	        {
	            return Long.valueOf(cellValue);
	        }
	        else if ((Float.TYPE == type) || (Float.class == type))
	        {
	            return Float.valueOf(cellValue);
	        }
	        else if ((Short.TYPE == type) || (Short.class == type))
	        {
	            return Short.valueOf(cellValue);
	        }
	        else if ((Double.TYPE == type) || (Double.class == type))
	        {
	            return Double.valueOf(cellValue);
	        }else if(Date.class == type){
//	        	if(format == null) format = new SimpleDateFormat("yyyy/MM/dd");
				return DateUtils.getDate(cellValue);
	        }else{
	        	throw new ClassCastException("excel解析字段类型匹配错误,不存在的javabean解析类型 : " + type.getName() + " " + fieldName);
	        }
		} catch (ValidateEntityException e) {
			throw e;
		} catch (Exception e) {
			log.debug(" 类型解析错误! bean字段名称[{}] , 字段类型 {} , excel中值为{}" , fieldName , type.getName() , cellValue);
			log.error(fieldName + "字段类型excel解析错误 : java.lang.String -> " + type.getName());
			throw new ValidateEntityException(String.format("[{0}]类型错误", fieldName)) ;
		}
	}
	
	private String getCellValue(Sheet sheet,Integer rowIndex ,Integer colIndex){
		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        return sheet.getRow(fr).getCell(fc).getStringCellValue();
	    }
	    if(sheet.getRow(rowIndex).getCell(colIndex) == null){
	    	return null;
	    }
	    sheet.getRow(rowIndex).getCell(colIndex).setCellType(Cell.CELL_TYPE_STRING);
	    return sheet.getRow(rowIndex).getCell(colIndex).getStringCellValue();
	}
	private String getCellValue(Sheet sheet, Cell cell){
		//if(cell.getCellTypeEnum() == CellType.NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)){// 3.17
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC && HSSFDateUtil.isCellDateFormatted(cell)){// 3.17
			// 参考文章 : https://blog.csdn.net/Steven_sf/article/details/78801971
//			if (cell.getCellTypeEnum() == CellType.STRING) { 
//		        setMethod.invoke(tObject, cell.getStringCellValue());
//		    } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
//		        // 判断参数类型
//		        if(HSSFDateUtil.isCellDateFormatted(cell)){
//		            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//		            setMethod.invoke(tObject, sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())));
//		        }else {
//		            setMethod.invoke(tObject, df.format(cell.getNumericCellValue()));
//		        }
//		    }
			log.error("dsadsa");
			Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
			log.error("" + date);
		}
		// cell.setCellType(CellType.STRING);3.17 
		cell.setCellType(Cell.CELL_TYPE_STRING);
		
		int rowIndex = cell.getRowIndex();
		int colIndex = cell.getColumnIndex();

		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        return sheet.getRow(fr).getCell(fc).getStringCellValue();
	    }
	    return cell.getStringCellValue().trim();
	}
	String isCombineCell(Sheet sheet , Cell cell){
		int rowIndex = cell.getRowIndex();
		int colIndex = cell.getColumnIndex();

		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        if(lr > fr){
	        	return ROW_COMBINE;
	        }else if(lc > fc){
	        	return ROW_COLUMN_COMBINE;
	        }
	        return COLUMN_COMBINE;
	    }
	    return NOT_COMBINE;
	}
	Boolean isColumnCombineCell(Sheet sheet , Cell cell){
		int rowIndex = cell.getRowIndex();
		int colIndex = cell.getColumnIndex();

		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        if(lc > fc){
	        	return Boolean.TRUE;
	        }
	        return Boolean.FALSE;
	    }
	    return Boolean.FALSE;
	}
	Boolean isRowCombineCell(Sheet sheet , Cell cell){
		int rowIndex = cell.getRowIndex();
		int colIndex = cell.getColumnIndex();

		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        if(lr > fr){
	        	return Boolean.TRUE;
	        }
	        return Boolean.FALSE;
	    }
	    return Boolean.FALSE;
	}
	private Integer getCurrRowspan(Sheet sheet, Cell cell) {
		int rowIndex = cell.getRowIndex();
		int colIndex = cell.getColumnIndex();
		
		// 遍历合并区域
	    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
	        CellRangeAddress region = sheet.getMergedRegion(i); 
	        int fc = region.getFirstColumn();             
	        int lc = region.getLastColumn();
	        int fr = region.getFirstRow();                   
	        int lr = region.getLastRow();                   
	        if(rowIndex<fr||rowIndex>lr||colIndex>lc||colIndex<fc)
	        	continue;
	        return lr-rowIndex;
	    }
		return 0;
	}
	
	/**
	 * -- 需指定标题行,然后还有就是空行处理
	 * @param workbookInputStream
	 * @param outputStream
	 * @param list
	 * @param titleRowIndex
	 * @throws Exception
	 */
	@Deprecated
	public void error(InputStream workbookInputStream , OutputStream outputStream ,List<? extends ValidableEntity> list,Integer titleRowIndex) throws Exception{
		Workbook workbook;Row row;Cell cell;
		workbook = WorkbookFactory.create(workbookInputStream);
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setColor(Font.COLOR_RED);
		font.setBoldweight((short)1);
		// font.setBold(true); 3.17
		style.setFont(font);
		style.setWrapText(true);
		Sheet sheet = workbook.getSheetAt(0);
		short cellnumber = sheet.getRow(titleRowIndex).getLastCellNum();
		// 原始处理方法 , 出现空行就无法正确处理
		Boolean ignoreNullDataRow = true;//需要空行处理的时候设置该参数即可 , 但是需要多测试测试空处理
		if(ignoreNullDataRow){
			for (int i = 0; i < list.size(); i++) {
				if(list.get(i).hasError() && list.get(i).getErrorMsg()!=null){
					row = sheet.getRow(titleRowIndex + i + 1);
					if(row == null){
						row = sheet.createRow(titleRowIndex + i + 1);
						cell = row.createCell(cellnumber);
						cell.setCellStyle(style);
						cell.setCellValue("如果下面的部分行报错信息不完整,请联系管理员,打开空行处理开关!");
						continue;
					}
					cell = row.createCell(cellnumber);
					cell.setCellStyle(style);
					cell.setCellValue(list.get(i).getErrorMsg().toString());
				}
			}
		}else{
			//将错误信息压栈 , 一次取出即可 ; 注意控制好空行数 , 免得出死循环_bug
			int nullRowCounts = 0 ;
			LinkedList<String> heapMessages = new LinkedList<String>();
			for (int i = 0; i < list.size() || heapMessages.size()>0  ; i++) {
				if(heapMessages.size() == 0 && i >= list.size())
					break;
				//只允许20左右的空行
				if(nullRowCounts > 20)
					break;
				if(i < list.size() && list.get(i).getErrorMsg()!=null){
					heapMessages.add(list.get(i).getErrorMsg().toString());
				}
				if(heapMessages.size() > 0){
					row = sheet.getRow(titleRowIndex + i + 1);
					if(row == null){
						nullRowCounts ++ ;
						continue;
					}
					cell = row.createCell(cellnumber);
					cell.setCellStyle(style);
					cell.setCellValue(heapMessages.poll());
				}
			}
		}
		list=null;
		workbook.write(outputStream);
		outputStream.flush();
		outputStream.close();
		workbookInputStream.close();
	}
	/**
	 * -- 和上面的比起来,这个类是将错误固定到指定的行
	 * @param workbookInputStream
	 * @param outputStream
	 * @param list
	 * @param titleRowIndex
	 * @throws Exception
	 */
	public void errorLookupRow(InputStream workbookInputStream , OutputStream outputStream ,List<? extends ExcelHelper> list) throws Exception{
		if(list == null || list.isEmpty()) {
			return ;
		}
		Workbook workbook = WorkbookFactory.create(workbookInputStream);
		CellStyle style = workbook.createCellStyle();
		Font font = workbook.createFont();
		font.setColor(Font.COLOR_RED);
		font.setBoldweight((short)1);
		// font.setBold(true); 3.17
		style.setFont(font);
//		style.setWrapText(true); // 是否折叠字符
		Sheet sheet = workbook.getSheetAt(0);
		short cellnumber = sheet.getRow(0).getLastCellNum(); // 0随机弄的
//		sheet.setColumnWidth(cellnumber, 20*256); // 设置错误列宽度 20个字符(20*256)
//		sheet.getRow(0).setHeight(sheet.getRow(0).getHeight());  // setWrapText == false 就不需要高度了,,和以前一直
		Cell errorCell = sheet.getRow(0).createCell(cellnumber);
		errorCell.setCellValue("请查看错误,并修改");
		errorCell.setCellStyle(style);
		for (ExcelHelper helper : list) {
			if(helper.hasError()) {
				Row row = sheet.getRow(helper.getRowNum());
				Cell cell = row.createCell(cellnumber);
				cell.setCellStyle(style);
				cell.setCellValue(helper.getErrorMsg());
			}
		}
		
		list=null;
		workbook.write(outputStream);
		outputStream.flush();
		outputStream.close();
		workbookInputStream.close();
	}
	
	/**
	 * -- 下载,将对象转换成一个
	 * @throws IOException 
	 * @throws InvalidFormatException 
	 * @throws IllegalAccessException 
	 * @throws IllegalArgumentException 
	 */
	// 简单的尝试~需要的时候在开发
	@Deprecated
	public static void toExcel(List<?> list , OutputStream outputStream) throws Exception {
		if(list == null || list.isEmpty()) {
			throw new IllegalArgumentException(" 传入的对象为空 ");
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet();
		HSSFCellStyle cellStyle = workbook.createCellStyle();
//		cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 获取行标题
		Field[] fields = list.get(0).getClass().getDeclaredFields();
		List<Field> excelFields = new ArrayList<>();
		List<String> titles = new ArrayList<>();
		for (Field field : fields) {
			if(field.isAnnotationPresent(ExcelField.class)) {
				String title = field.getAnnotation(ExcelField.class).value();
				titles.add(title);
				excelFields.add(field);
				field.setAccessible(true);
			}
		}
		
		if(titles.isEmpty()) {
			throw new IllegalArgumentException(" 没有ExcelField的属性 ");
		}
		HSSFRow row = sheet.createRow(0);
		row.setHeight((short) (20*20));
		for (int i = 0; i < titles.size(); i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellValue(titles.get(i));
			cell.setCellStyle(cellStyle);
		}
		
		// 标题设置完成后,设置数据
		
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(1+i);
			for (int j = 0; j < excelFields.size(); j++) {
				Object object = excelFields.get(j).get(list.get(i));
				if(object == null) {
					object = "";
				}
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(object.toString());
			}
		}
		
		workbook.write(outputStream);
		outputStream.flush();
		outputStream.close();
	}
	
}
